package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.heaven.entity.StudentCourse;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 学生选课Dao
 * Created by fanruiping on 2015/3/21.
 */
@Repository
public class StudentCourseDao extends BaseDao {
    /**
     * 获取指定课程群组下的学生数量
     * @param courseGroupId 课程群组编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-03-21
     */
    public int getStudentCntByCourse(Long courseGroupId){
        String sql = "SELECT COUNT(id) FROM t_student_course WHERE sCourseCode =(SELECT sCode FROM " +
                "t_course_group WHERE id=:courseGroupId)";

        return namedParameterJdbcTemplate.queryForObject(sql,new MapSqlParameterSource().addValue("courseGroupId",courseGroupId),Integer.class);
    }

    /**
     * 全部删除学生选课
     * @return int
     *
     * @author wyh
     * @since 2015-04-01
     */
    public int deleteAll(){
        String sql = "DELETE FROM t_student_course";

        log.debug("return 删除学生选课数据的结果");
        return namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource());
    }

    /**
     * 批量插入学生选课
     * @param scList 学生选课信息集合
     * @return int[]
     *
     * @author wyh
     * @since 2015-03-24
     */
    public int[] saveSC(final List<StudentCourse> scList){
        String sql = "INSERT INTO t_student_course VALUES(NULL, :courseCode, :studentNum)";

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(scList.toArray());
        log.debug("return 批量新增学生选课数据的结果");
        return namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    /**
     * 批量删除学生选课
     * @param scList 学生选课信息集合
     * @return int[]
     *
     * @author wyh
     * @since 2015-03-24
     */
    public int[] deleteSC(final List<StudentCourse> scList){
        String sql = "DELETE FROM t_student_course WHERE sCourseCode=:courseCode AND sStudentNum=:studentNum";

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(scList.toArray());
        log.debug("return 批量删除学生选课数据的结果");
        return namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    /**
     * 查询学生的课程代码list
     * @param studentNum 学号
     * @return List
     *
     * @author wyh
     * @since 2015-04-02
     */
    public List<String> getCourseCodes(String studentNum){
        String sql = "SELECT sCourseCode FROM t_student_course WHERE sStudentNum=:studentNum";

        log.debug("返回 查询学生课程代码list");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("studentNum", studentNum), String.class);
    }
}
